USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MAESTRO_CREDITO]    Script Date: 11/24/2011 14:33:34 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0002_MAESTRO_CREDITO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0002_MAESTRO_CREDITO]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MAESTRO_CREDITO]    Script Date: 11/24/2011 14:33:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0002_MAESTRO_CREDITO]
AS
BEGIN  
	SELECT	DISTINCT p.z07327AGE cragencia,
			a.Crcredito,
			a.CRMARCA,
			a.CRRUTDEUDOR AS CRRUTDEUDO,
			a.RUTDEUDOR AS RUTDEUDOR,
			a.TIPO_CLIENTE,
			a.CRDVDEUDOR AS CRDVEDEUDO,
			substring(c.codeudor1,1,len(c.codeudor1)-1) AS CRRUTCOD1, 
			substring(c.codeudor1,len(c.codeudor1),1) AS CRDVECOD1, 
			substring(c.codeudor2,1,len(c.codeudor2)-1) AS CRRUTCOD2, 
			substring(c.codeudor2,len(c.codeudor2),1) AS CRDVECOD2, 
			P.clave  AS CRRUTEMPRE,
			null AS CRDVEEMPRE,
			a.CRCLASE,
			a.CRCUOTAS,
			a.CRFECAPROB,
			CRVALCUOTA =
			CASE 
				WHEN (a.MONCRED = 'UF') THEN ((k.valcuot / 100)* a.uf)
				WHEN (a.MONCRED = 'PE') THEN k.valcuot
			END,
			f.diasprim AS CRDIASPRIM,  
			a.CRMONTO,
			CRORIGEN =
			CASE
				WHEN(a.MONCRED = 'UF') THEN (CRORIGEN/100  * a.uf)
				ELSE NULL
			END,
			CRSEGURO = 
			CASE
				WHEN(a.MONCRED = 'UF') THEN Cast(((e.val_seg /100)* a.uf) AS NUMERIC)
				ELSE e.val_seg
			END, 
			a.CRTASA,
			a.CRESPECIAL,
			A.CRMODIFICA,
			A.STSMODREGC,
			A.FMODIFREGC,
			A.USRMODREGC,
			A.CRINDICA,
			A.CRSERIE,
			g.notario as CRNOTARIO,
			CRIMPUESTO = 
			CASE    
				WHEN(a.MONCRED = 'UF')THEN Cast(((h.impuesto /100)*a.uf) AS NUMERIC)
				WHEN(a.MONCRED = 'PE') THEN h.impuesto
			END, 
			j.comision as CRCOMISION,
			--CONVERT(VARCHAR,b.ult_pago,112) as CRFECUPAGO,
			A.USRMODAPRO,
			A.CRFECRELIQ,
			A.USRMODRELI,
			--CONVERT(VARCHAR,k.prvencim,112) as CRFECPRVEN,
			A.CRGASTOPER,
			CRMONABONA = 
			CASE
				WHEN (t.Z07394IMP is null)THEN 0
				ELSE t.Z07394IMP
			END,
			A.CRESTADO,
			CRSEGDESGR = 
			CASE
				WHEN(a.MONCRED = 'UF')THEN Cast(((degrav.val_seg /100)* a.uf) AS NUMERIC)
				ELSE degrav.val_seg
			END, 
			vigente as CRSTSVGTE,
			CRSTSMORA = 
			CASE 
				WHEN (D.tipo_mora = 'P') THEN 1
				WHEN (d.tipo_mora = 'E') THEN 2
				ELSE 0
			END,
			a.CRAGECOLOC, 
			a.FILLER,
			q.ENDeu ENDEUD_MAXIMO,
			q.disponible,
			q.ubnom,
			q.analista ,
			r.est AS ESTADOTRAB,
			s.MTOLIQ , 
			a.cancelado,
			u.TIPCRED AS TIPO_CREDITO,
			v.z073GKPRE AS Solicitante,
			p.R1cta  AS CTABANTOTAL,
			CRSEGCesantia = 
			CASE
				WHEN(a.MONCRED   = 'UF') THEN Cast(((xx.val_seg  /100)* a.uf) AS NUMERIC)
				ELSE xx.val_seg 
			END
	INTO	#CSPF000_TEMP	
	--into  dbo.CSPF000_TEMP
	FROM    dbo.MaestroCredito a LEFT OUTER JOIN
            dbo.ult_pago b ON a.CRAGECOLOC = b.suc AND a.crrutdeudor = b.cta AND a.crcredito = b.oper LEFT OUTER JOIN
            dbo.codeudores c ON a.CRAGECOLOC = c.suc AND a.crrutdeudor = c.cta AND a.crcredito = c.oper LEFT OUTER JOIN
            dbo.mora d ON a.CRAGECOLOC = d.suc AND a.crrutdeudor = d.cta AND a.crcredito = d.oper LEFT OUTER JOIN
            dbo.crseguros e ON a.CRAGECOLOC = e.suc AND a.crrutdeudor = e.cta AND a.crcredito = e.ope LEFT OUTER JOIN
            dbo.diasprim f ON a.CRAGECOLOC = f.suc AND a.crrutdeudor = f.cta AND a.crcredito = f.oper LEFT OUTER JOIN
            dbo.notaria g ON a.CRAGECOLOC = g.suc AND a.crrutdeudor = g.cta AND a.crcredito = g.ope LEFT OUTER JOIN
            dbo.impuesto h ON a.CRAGECOLOC = h.suc AND a.crrutdeudor = h.cta AND a.crcredito = h.ope LEFT OUTER JOIN
            dbo.seguro_degr degrav ON a.CRAGECOLOC = degrav.ppsuc AND a.crrutdeudor = degrav.ppcta AND a.crcredito = degrav.ppoper LEFT OUTER JOIN
            dbo.comisiones j ON a.CRAGECOLOC = j.suc AND a.crrutdeudor = j.cta AND a.crcredito = j.oper LEFT OUTER JOIN
            dbo.valor_cuota k ON a.CRAGECOLOC = k.suc AND a.crrutdeudor = k.cta AND a.crcredito = k.oper LEFT OUTER JOIN
            dbo.rut_emp m ON a.CRAGECOLOC = m.suc AND a.crrutdeudor = m.cta AND a.crcredito = m.oper LEFT OUTER JOIN
            dbo.clave_emp p ON a.CRAGECOLOC = p.aosuc AND a.crrutdeudor = p.aocta AND a.crcredito = p.aooper LEFT OUTER JOIN
            dbo.ENDeuda_maximo q ON a.CRAGECOLOC = q.suc AND a.crrutdeudor = q.cta AND a.crcredito = q.oper LEFT OUTER JOIN
            dbo.est_trabajador r ON a.crrutdeudor = r.cta LEFT OUTER JOIN
            dbo.monto_liq s ON a.crcredito = s.aooper AND a.crrutdeudor = s.aocta AND a.CRAGECOLOC = s.aosuc LEFT OUTER JOIN
            dbo.as_refundidos t ON a.crrutdeudor = t.aocta AND a.crcredito = t.Z07391Op1 AND a.CRAGECOLOC = t.Z07391su1 LEFT OUTER JOIN
            dbo.tipo_credito_mc u ON a.CRAGECOLOC = u.aosuc AND a.crcredito = u.aooper AND a.crrutdeudor = u.aocta LEFT OUTER JOIN
            dbo.as_solicitante v ON a.CRAGECOLOC = v.aosuc AND a.crrutdeudor = v.aocta AND a.crcredito = v.aooper LEFT OUTER JOIN
            dbo.vigente ww ON a.CRAGECOLOC = ww.suc AND a.crrutdeudor = ww.cta AND a.crcredito = ww.oper LEFT OUTER JOIN
            dbo.seguro_ces xx ON a.CRAGECOLOC = xx.suc AND a.crrutdeudor = xx.cta AND a.crcredito = xx.ope
		
	SELECT	cragencia,
			Crcredito,
			CRMARCA,
			CRRUTDEUDO,
			RUTDEUDOR,
			TIPO_CLIENTE,
			CRDVEDEUDO,
			CRRUTCOD1, 
			CRDVECOD1, 
			CRRUTCOD2, 
			CRDVECOD2, 
			CRRUTEMPRE,
			CRDVEEMPRE,
			CRCLASE,
			CRCUOTAS,
			CRFECAPROB,
			CRVALCUOTA,
			CRDIASPRIM,  
			CRMONTO,
			CRORIGEN,
			CRSEGURO, 
			CRTASA,
			CRESPECIAL,
			CRMODIFICA,
			STSMODREGC,
			FMODIFREGC,
			USRMODREGC,
			CRINDICA,
			CRSERIE,
			CRNOTARIO,
			CRIMPUESTO, 
			CRCOMISION,
			USRMODAPRO,
			CRFECRELIQ,
			USRMODRELI,
			CRGASTOPER,
			CRMONABONA,
			CRESTADO,
			CRSEGDESGR, 
			CRSTSVGTE,
			CRSTSMORA,
			CRAGECOLOC, 
			FILLER,
			ENDEUD_MAXIMO,
			disponible,
			ubnom,
			analista ,
			ESTADOTRAB,
		--	MTOLIQ , 
			Cast(CRMONTO -  (CASE WHEN CRSEGCesantia is not null THEN CRSEGCesantia ELSE 0 END + CASE WHEN CRSEGDESGR is not null THEN CRSEGDESGR ELSE 0 END+  CASE WHEN CRSEGURO is not null THEN CRSEGURO ELSE 0 END +CASE WHEN CRIMPUESTO is not null THEN CRIMPUESTO ELSE 0 END+ CASE WHEN CRNOTARIO is not null THEN CRNOTARIO ELSE 0 END +CRMONABONA ) AS NUMERIC(11)) AS MTOLIQ,
			cancelado,
			TIPO_CREDITO,
			Solicitante,
			CTABANTOTAL,
        	CRSEGCesantia,
			0 as crsegsalud
	INTO	dbo.CSPF000_TEMP
	FROM	#CSPF000_TEMP	

	UPDATE	CSPF000_TEMP 
	SET		crsegsalud = val_seg
	FROM	seguro_salud
	WHERE	cta = CRRUTDEUDO and
			ope = CRCREDITO and
			suc = CRAGECOLOC
END

GO


